using System;
using System.Reflection;
using System.Xml;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;	
using System.Data.SqlClient;
using IO = System.IO;
using System.Text;
using System.Runtime.InteropServices;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using Microsoft.ApplicationBlocks.Data;
using ADODB;
using Dimok;
using Dimok.Exceptions;

////////////////////////////////////////////////////////////////////////////////////////////////////
// namespace: Dimok.Data.Database
//
// summary:	.
////////////////////////////////////////////////////////////////////////////////////////////////////

namespace Dimok.Data.Database{

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Where filter. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public class WhereFilter{
		public readonly string Filter;
		public readonly object[] Params;

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Constructor. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="filter">				The filter. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public WhereFilter(string filter,ArrayList paramsArray):this(filter,paramsArray.ToArray()){}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Constructor. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="filter">				The filter. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public WhereFilter(string filter,object[] paramsArray){
			Filter = filter;
			Params = paramsArray;
		}
	}

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Update struct. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public struct UpdateStruct{

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Constructor. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="sql">				The sql. </param>
		/// <param name="sqlParams">	Options for controlling the sql. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public UpdateStruct(string sql,SqlParameter[] sqlParams){
			this.sql = sql;
			this.sqlParams = sqlParams;
		}
		public string sql;
		public SqlParameter[] sqlParams;
	}

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Sql helper ex. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public class SQLHelperEx:SqlHelper{

		#region Overloads

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Fill sp command. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="spName">						Name of the sp. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static SqlCommand FillSpCommand(string connectionString, string spName) {
			using (SqlConnection cn = new SqlConnection(connectionString)){
				SqlCommand cmd = new SqlCommand(spName,cn);
				cn.Open();
				cmd.CommandType = CommandType.StoredProcedure;
				SqlCommandBuilder.DeriveParameters(cmd);
				return cmd;
			}
		}
		#endregion Overloads

		#region ADO

		#region DS to RS

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sto a do. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="table">	The table. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass DStoADO(DataTable table){
			ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
			foreach( DataColumn col in table.Columns )
				DataTypeMap(col,table.Rows,rs);// rs.Fields.Append(col.ColumnName,ADODB.DataTypeEnum.adVarChar,900000,ADODB.FieldAttributeEnum.adFldUnspecified,Missing.Value);
			rs.Open(Missing.Value,Missing.Value,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockOptimistic,0);
			foreach( DataRow row in table.Rows ){
				rs.AddNew(Missing.Value,Missing.Value);
				foreach( DataColumn col in table.Columns )
					try{
						ADODB.Field field = rs.Fields[col.ColumnName];
						field.Value = row[col];
					}catch(Exception exc){
						throw new Exception("Column:"+col.ColumnName+",Value:"+row[col],exc);
					}
			}
			return rs;
		}
/*
			DataTable dtADO = SQLHelperEx.ExecuteDatasetEx(conn,"SELECT * FROM ADO").Tables[0];
			ADODB.Recordset rs = SQLHelperEx.ExecuteADO(connADO,"SELECT * FROM ADO");
			string map = "";
			for( int i=0; i < dtADO.Columns.Count; i++ ){
				map += "Name:"+dtADO.Columns[i].ColumnName+",Type:"+dtADO.Columns[i].DataType.Name+" - Type:"+rs.Fields[i].Type+",Attrs:"+rs.Fields[i].Attributes+",Size:"+rs.Fields[i].DefinedSize+Environment.NewLine;
			}

*/

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Data type map. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="col">	The col. </param>
		/// <param name="rows">	The rows. </param>
		/// <param name="rs">		The rs. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void DataTypeMap(DataColumn col,DataRowCollection rows,ADODB.RecordsetClass rs){
			ADODB.DataTypeEnum dataType = ADODB.DataTypeEnum.adVarChar;
			int fieldSize = 8000;
			ADODB.FieldAttributeEnum fieldAttr = col.AllowDBNull ? ADODB.FieldAttributeEnum.adFldIsNullable : ADODB.FieldAttributeEnum.adFldUnspecified;
			switch(col.DataType.Name){
				case "String":
					int size = GetColumnSize(col,rows);
					if( size > 8000 ){
						dataType = ADODB.DataTypeEnum.adLongVarChar;
						fieldSize = 2147483647;
					}
					break;
				case "Int32":
					dataType =  ADODB.DataTypeEnum.adInteger;
					fieldSize =  4;
					break;
				case "DateTime":
					dataType = ADODB.DataTypeEnum.adDBTimeStamp;
					fieldSize = 16;
					break;
				case "Decimal":
					dataType = ADODB.DataTypeEnum.adCurrency;
					fieldSize = 8;
					break;
				case "Double":
					dataType = ADODB.DataTypeEnum.adDouble;
					fieldSize = 8;
					break;
				case "Byte":
					dataType = ADODB.DataTypeEnum.adUnsignedTinyInt;
					fieldSize = 1;
					break;
				case "Boolean":
					dataType = ADODB.DataTypeEnum.adBoolean;
					fieldSize = 2;
					break;
				case "Byte[]":
					dataType = ADODB.DataTypeEnum.adLongVarBinary;
					fieldSize = 2147483647;
					break;
				default:
					throw new Exception("ADO does not support ["+col.DataType.Name+"] data type.");
			}
			rs.Fields.Append(col.ColumnName,dataType,fieldSize,fieldAttr,Missing.Value);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a column size. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="column">	The column. </param>
		/// <param name="rows">		The rows. </param>
		///
		/// <returns>	The column size. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static int GetColumnSize(DataColumn column,DataRowCollection rows){
			int i = 0;
			foreach( DataRow row in rows )
				if( row[column].ToString().Length > i)i = row[column].ToString().Length;
			return i;
		}
		/*
		String - Type:adVarChar,Attrs:104,Size:50
		Int32 - Type:adInteger,Attrs:120,Size:4
		DateTime - Type:adDBTimeStamp,Attrs:120,Size:16
		Decimal - Type:adNumeric,Attrs:120,Size:19
		Byte - Type:adUnsignedTinyInt,Attrs:120,Size:1
		Boolean - Type:adBoolean,Attrs:120,Size:2
		String - Type:adLongVarChar,Attrs:232,Size:2147483647
		Byte[] - Type:adLongVarBinary,Attrs:232,Size:2147483647
		*/
		#endregion

		#region XSL_ADO
		public const string XSL_ADO = @"
			<xsl:stylesheet xmlns:xsl='http://www.w3.org/1999/XSL/Transform' version='1.0'
			xmlns:rs='urn:schemas-microsoft-com:rowset'
			xmlns:z='#RowsetSchema'>
			<xsl:output method='xml' indent='yes'/>
			<xsl:param name='tablename'/>
						<xsl:template match='NewDataSet'>
										<rs:data>
			<xsl:for-each select='./node()[local-name(.)=$tablename]'>
								<z:row>
			<xsl:for-each select='@*'>
								<xsl:copy-of select='.'/>
														</xsl:for-each>
			</z:row>
			</xsl:for-each>
			</rs:data>
			</xsl:template>
			</xsl:stylesheet>
			";
		#endregion

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp a do operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ApplicationException">	Thrown when application. </exception>
		///
		/// <param name="conn">	The connection. </param>
		/// <param name="sql">	The sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static RecordsetClass ExecSpADO(string conn,string sql){
			if( sql.IndexOf(";") >= 0 )throw new ApplicationException("Hacker alert!");
			RecordsetClass rs = new RecordsetClass();
			ADODB.Command comm = new ADODB.CommandClass();
			ADODB.ConnectionClass adoConn = new ConnectionClass();
			adoConn.CursorLocation = CursorLocationEnum.adUseClient;
			adoConn.Open(conn,"","",0);
			comm.ActiveConnection = adoConn;
			comm.CommandText = sql;
			comm.CommandType = CommandTypeEnum.adCmdStoredProc;
			object o = Missing.Value;
			object m = Missing.Value;
			rs = (RecordsetClass)comm.Execute(out o,ref m,0);
			//adoConn.Close();
			return rs;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes a do operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass ExecuteADO(string conn,string sql,params object[] paramsArray){
			return ExecuteADO(conn,60,sql,paramsArray);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes a do operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="COMException">	Thrown when an unrecognised HRESULT is returned from a COM
		/// 																method call. </exception>
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		/// <exception cref="Exception">		Thrown when exception. </exception>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="seconds">			The seconds. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass ExecuteADO(string conn,int seconds,string sql,params object[] paramsArray){
			try {
				CheckInjects(sql);
				if (paramsArray.Length == 1 && paramsArray[0].GetType().IsArray)
					paramsArray = (object[])paramsArray[0];
				object r = Missing.Value;
				object p = Missing.Value;
				ADODB.ConnectionClass adoConn = new ConnectionClass();
				ADODB.Command comm = new ADODB.CommandClass();
				adoConn.CursorLocation = CursorLocationEnum.adUseClient;
				adoConn.Open(conn, "", "", 0);
				comm.ActiveConnection = adoConn;
				comm.CommandText = sql;
				comm.CommandType = sql.ToUpper().IndexOf("SELECT") == 0 ? CommandTypeEnum.adCmdText : CommandTypeEnum.adCmdStoredProc;
				if (seconds > 0) comm.CommandTimeout = seconds;
				if (paramsArray.Length > 0) {
					if (sql.ToLower().StartsWith("sp__"))
						GetParams(comm, paramsArray);
					else
						try {
							comm.Parameters.Refresh();
						} catch (COMException exc) {
							if (exc.ErrorCode != -2147467259) throw exc;
							for (int par = 0; par < paramsArray.Length; par++) {
								ADODB.Parameter param = comm.CreateParameter("P" + par, new ADODataTypeInfo(paramsArray[par]).type, ADODB.ParameterDirectionEnum.adParamInput, new ADODataTypeInfo(paramsArray[par]).size, r);
								comm.Parameters.Append(param);
							}
						}
					for (int i = 0, j = 0; j < paramsArray.Length && i < comm.Parameters.Count; i++) {
						ADODB.Parameter param = comm.Parameters[i];
						if (param.Direction == ADODB.ParameterDirectionEnum.adParamReturnValue) continue;
						param.Value = paramsArray[j];
						j++;
					}
				}
				RecordsetClass rs = new RecordsetClass();
				try {
					rs.Open(comm, Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0);
				} catch (Exception exc) {
					throw new ASPException(exc, "SQL", sql);
				}
				//rs = comm.Execute(out r,ref p,0);
				return rs;
			} catch (Exception exc) {
				exc.Data.Add("SQL", sql);
				exc.Data.Add("Params", new StringList(paramsArray).Join());
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the parameters. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="comm">				The communications. </param>
		/// <param name="parameters">	. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void GetParams(ADODB.Command comm,params object[] parameters){
			for( int i = 0; i < parameters.Length; i++ )
				comm.Parameters.Append(comm.CreateParameter("P"+i,new ADODataTypeInfo(parameters[i]).type,ADODB.ParameterDirectionEnum.adParamInput,new ADODataTypeInfo(parameters[i]).size,parameters[i]));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Information about the ado data type. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private struct ADODataTypeInfo{
			public ADODB.DataTypeEnum type;
			public int size;
			public ADODataTypeInfo(object data){
				if( data.GetType() == typeof(string) ){
					type = ADODB.DataTypeEnum.adVarChar;
					size = data.ToString().Length;
					return;
				}
				if( data.GetType() == typeof(int) ){
					type = ADODB.DataTypeEnum.adInteger;
					size = 4;
					return;
				}
				throw new Exception("Unsupported data type.");
			}
		}
			#region AppendRS

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Appends the rs to string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="seconds">			The seconds. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string AppendRSToString(string conn,int seconds,string sql,params object[] paramsArray){
			return ADOToString(AppendRS(conn,seconds,sql,paramsArray));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Appends the rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass AppendRS(string conn,string sql,params object[] paramsArray){
			return AppendRS(ExecuteADO(conn,sql,paramsArray));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Appends the rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="seconds">			The seconds. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass AppendRS(string conn,int seconds,string sql,params object[] paramsArray){
			return AppendRS(ExecuteADO(conn,seconds,sql,paramsArray));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Appends the rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="rs">	The rs. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass AppendRS(ADODB.RecordsetClass rs){
			object recsAffected;
			ADODB.RecordsetClass rsNext = (RecordsetClass)rs.NextRecordset(out recsAffected);
			if( rsNext == null )return rs;
			ADODB.RecordsetClass rsClone = AppendRS(null,rs);
			do{	rsClone = AppendRS(rsClone,rsNext);	}while( (rsNext=(RecordsetClass)rs.NextRecordset(out recsAffected)) != null );
			return rsClone;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Clone rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="rs">	The rs. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static ADODB.RecordsetClass CloneRS(ADODB.RecordsetClass rs){
			ADODB.RecordsetClass rsOut = new ADODB.RecordsetClass();
			foreach( ADODB.Field f in rs.Fields ){
				rsOut.Fields.Append(f.Name,f.Type,f.DefinedSize,ADODB.FieldAttributeEnum.adFldIsNullable,Missing.Value);
				rsOut.Fields[rsOut.Fields.Count-1].NumericScale = f.NumericScale;
				rsOut.Fields[rsOut.Fields.Count-1].Precision = f.Precision;
				rsOut.Fields[rsOut.Fields.Count-1].Attributes = f.Attributes;
			}
			rsOut.Open(Missing.Value,Missing.Value,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockBatchOptimistic,0);
			return rsOut;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Appends the rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="rsTo">		The rs to. </param>
		/// <param name="rsFrom">	The rs from. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static ADODB.RecordsetClass AppendRS( ADODB.RecordsetClass rsTo,ADODB.RecordsetClass rsFrom){
			if( rsFrom.State != 1 )return rsTo;
			if( rsTo == null )rsTo = CloneRS(rsFrom);
			if( rsFrom.EOF )return rsTo;
			for( rsFrom.MoveFirst() ; !rsFrom.EOF; rsFrom.MoveNext() ){
				rsTo.AddNew(Missing.Value,Missing.Value);
				foreach( ADODB.Field f in rsFrom.Fields )
					rsTo.Fields[f.Name].Value = f.Value;
			}
			return rsTo;
		}
			#endregion

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes a do old operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ApplicationException">	Thrown when application. </exception>
		///
		/// <param name="conn">	The connection. </param>
		/// <param name="sql">	The sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static RecordsetClass ExecuteADO_Old(string conn,string sql){
			if( sql.IndexOf(";") >= 0 )throw new ApplicationException("Hacker alert!");
			ADODB.ConnectionClass connObj = new ConnectionClass();
			connObj.CommandTimeout = 60;
			connObj.Open(conn,"","",-1);
			object recs;
			return (RecordsetClass)connObj.Execute(sql,out recs,-1);
			//Recordset rs = new RecordsetClass();
			//rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
			//rs.Open(sql,conn,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockBatchOptimistic,0);
			//return rs;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Ado to string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="TooManyRecordsException">	Thrown when toomanyrecords. </exception>
		///
		/// <param name="maxRecords">		The maximum records. </param>
		/// <param name="conn">					The connection. </param>
		/// <param name="seconds">			The seconds. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string ADOToString(int maxRecords,string conn,int seconds,string sql,params object[] paramsArray){
			ADODB.RecordsetClass rs = ExecuteADO(conn,seconds,sql,paramsArray);
			if( maxRecords > 0 && rs.RecordCount > maxRecords )
				throw new TooManyRecordsException(rs.RecordCount);
			return ADOToString(rs);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Ado to string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="seconds">			The seconds. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string ADOToString(string conn,int seconds,string sql,params object[] paramsArray){
			return ADOToString(0,conn,seconds,sql,paramsArray);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Ado to string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string ADOToString(string conn,string sql,params object[] paramsArray){
			try {
				return ADOToString(conn, 0, sql, paramsArray);
			} catch (Exception exc) {
				throw new ASPException(exc, "SQL", sql, "Params", new StringList(paramsArray));
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Ado to string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="rs">	The rs. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string ADOToString(ADODB.RecordsetClass rs){
			try{
				Stream strm = new StreamClass();
				strm.Type = ADODB.StreamTypeEnum.adTypeText;
				strm.Open(Missing.Value,ADODB.ConnectModeEnum.adModeUnknown,ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified,"","");
				rs.Save(strm,ADODB.PersistFormatEnum.adPersistXML);
				return strm.ReadText(-1);
			}finally{
				try{
					if( rs.ActiveConnection != null )
						((Connection)rs.ActiveConnection).Close();
				}catch{}
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	String to a do. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="text">	The text. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass StringToADO(string text){
			ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
			Stream strm = new StreamClass();
			strm.Type = ADODB.StreamTypeEnum.adTypeText;
			strm.Open(Missing.Value,ADODB.ConnectModeEnum.adModeUnknown,ADODB.StreamOpenOptionsEnum.adOpenStreamUnspecified,"","");
			strm.WriteText(text,ADODB.StreamWriteEnum.adWriteChar);
			strm.Position = 0;
			rs.Open(strm,Missing.Value,ADODB.CursorTypeEnum.adOpenStatic,ADODB.LockTypeEnum.adLockBatchOptimistic,0);
			return rs;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a do string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	a do string. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetADOString(string conn,string sql,params object[] paramsArray){
				throw new Exception("GetADOString is not implemented.");
				//return ConvertToRs.GetADORS(ExecuteSafeDataSet(conn,sql,paramsArray),"AFC");
		}
		#endregion ADO

		#region TDC

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a tdc string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">	. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="delimiter">	The delimiter. </param>
		///
		/// <returns>	The tdc string. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetTDCString(string connection,string sql,string delimiter){
			const string FIELD_DELIM = "|";
			if( delimiter == "" )delimiter = FIELD_DELIM;
			DataTable dt = ExecuteDatasetEx(connection,sql).Tables[0];
			string[] aHdr = new string[dt.Columns.Count];
			for( int i=0; i < dt.Columns.Count; i++ )
				aHdr[i] = dt.Columns[i].ColumnName;
			string column = string.Join("+'"+delimiter+"'+",aHdr);
			dt.Columns.Add("TDC",System.Type.GetType("System.String"),column);
			string[] aSQL = new string[dt.Rows.Count];
			for( int i=0; i < dt.Rows.Count; i++ )
				aSQL[i] = dt.Rows[i]["TDC"].ToString();
			return string.Join(delimiter,aHdr)+"\n"+string.Join("\n",aSQL);
		}
		#endregion

		#region Extentions

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the safe data set operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">					The connection. </param>
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecuteSafeDataSet(string conn,string sql,params object[] paramsArray){
			using( SqlConnection c = new SqlConnection(conn) ){
				c.Open();
				SqlTransaction tran = c.BeginTransaction(IsolationLevel.ReadUncommitted);
				return IsSPName(CheckInjects(sql)) ?
					ExecuteDataset(tran,sql,paramsArray) : ExecuteDataset(tran,CommandType.Text,sql,PrepCommand(sql,paramsArray));
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">					. </param>
		/// <param name="customTable">				The custom table. </param>
		/// <param name="commandType">				Type of the command. </param>
		/// <param name="sql">								The sql. </param>
		/// <param name="commandParameters">	Options for controlling the command. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable ExecuteDatasetEx(string connection, DataTable customTable, CommandType commandType, string sql, params object[] commandParameters) {
			using(SqlConnection conn = new SqlConnection(connection)){
				return ExecuteDatasetEx(conn,customTable,commandType,sql,PrepCommand(sql,commandParameters));
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connection">					. </param>
		/// <param name="customTable">				The custom table. </param>
		/// <param name="commandType">				Type of the command. </param>
		/// <param name="commandText">				The command text. </param>
		/// <param name="commandParameters">	Options for controlling the command. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable ExecuteDatasetEx(SqlConnection connection, DataTable customTable, CommandType commandType, string commandText, params SqlParameter[] commandParameters) {
			SqlCommand cmd = new SqlCommand();
			try {
				SqlHelper.PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
				new SqlDataAdapter(cmd).Fill(customTable);
				cmd.Parameters.Clear();
				return customTable;						
			}catch(SqlException exc){
				FillExceptionWithCommandInfo(exc, cmd);
				throw exc;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="dtCustom">					The dt custom. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable ExecuteDatasetEx(string connectionString,DataTable dtCustom, string sql, params object[] parameterValues){
			using(SqlConnection conn = new SqlConnection(connectionString)){
				conn.Open();
				return ExecuteDatasetEx(conn,dtCustom,sql,parameterValues);
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connection">				. </param>
		/// <param name="dtCustom">					The dt custom. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable ExecuteDatasetEx(SqlConnection connection,DataTable dtCustom, string sql, params object[] parameterValues){
			SqlCommand cmd = new SqlCommand();
			try {

				SqlParameter[] commandParameters = IsSPName(sql) ?
					SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, sql) :
					PrepCommand(sql,parameterValues);
				if( IsSPName(sql) )AssignParameterValues(commandParameters, parameterValues);

				PrepareCommand(cmd, connection, (SqlTransaction)null, GetCommandType(sql), sql, commandParameters);
				SqlDataAdapter da = new SqlDataAdapter(cmd);
				da.Fill(dtCustom);
				cmd.Parameters.Clear();
				return dtCustom;
			} catch (SqlException exc) {
				FillExceptionWithCommandInfo(exc, cmd);
				throw exc; 
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandTimeout">		The command timeout. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecuteDatasetEx(string connectionString,int commandTimeout, string sql, params object[] parameterValues){
			SqlCommand cmd = new SqlCommand();
			try {
				using(SqlConnection connection = new SqlConnection(connectionString)){
					connection.Open();
					SqlParameter[] commandParameters = IsSPName(sql) ?
						SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, sql) :
						PrepCommand(sql,parameterValues);
					if( IsSPName(sql) )AssignParameterValues(commandParameters, parameterValues);

					PrepareCommand(cmd, connection, (SqlTransaction)null, GetCommandType(sql), sql, commandParameters);
					cmd.CommandTimeout = commandTimeout;
					DataSet ds = new DataSet();
					while (true)
						try {
							SqlDataAdapter da = new SqlDataAdapter(cmd);
							da.Fill(ds);
							break;
						} catch (SqlException exc) {
							if (exc.Number != 1205) throw exc;
							connection.Close();
							ASPException.Publish(exc, "SQL(DeadLock)", sql, "Params", new StringList(parameterValues));
							connection.Open();
						}

					cmd.Parameters.Clear();
					return ds;
				}
			} catch (SqlException exc) {
				exc.Data["Connection"] = connectionString;
				FillExceptionWithCommandInfo(exc, cmd);
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecuteDatasetEx(string connectionString, string sql, params object[] parameterValues){
			try{
				while (true)
					try {
						if (!IsSPName(sql)) return ExecuteDatasetEx(connectionString, CommandType.Text, sql, parameterValues);
						return ExecuteDataset(connectionString, sql, parameterValues);
					} catch (SqlException exc) {
						if (exc.Number != 1205) throw exc;
						ASPException.Publish(exc, "SQL(DeadLock)", sql, "Params", new StringList(parameterValues)+"");
					}
			} catch (SqlException exc) {
				if (!exc.Data.Contains("SQL")) exc.Data.Add("SQL", sql);
				if (!exc.Data.Contains("Params")) exc.Data.Add("Params", new StringList(parameterValues)+"");
				throw exc;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the dataset ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandType">			Type of the command. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecuteDatasetEx(string connectionString,CommandType commandType, string sql, params object[] parameterValues){
			try{
				return ExecuteDataset(connectionString,commandType,sql,PrepCommand(sql,parameterValues));
			}catch(SqlException exc){
				if (!exc.Data.Contains("SQL")) exc.Data.Add("SQL", sql);
				if (!exc.Data.Contains("Params")) exc.Data.Add("Params", new StringList(parameterValues)+"");
				throw exc;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the non query ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="FillExceptionWithCommandInfo">	Thrown when fill exception with command
		/// 																								information. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandTimeout">		The command timeout. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void ExecuteNonQueryEx(string connectionString, int commandTimeout, string sql, params object[] parameterValues){
			using(SqlConnection connection = new SqlConnection(connectionString)){
				connection.Open();
				SqlCommand cmd = new SqlCommand();
				try {
					if( commandTimeout >= 0 )cmd.CommandTimeout = commandTimeout;
					SqlHelper.PrepareCommand(cmd, connection, (SqlTransaction)null, CommandType.Text, sql, PrepCommand(sql, parameterValues));
					cmd.ExecuteNonQuery();
				} catch (Exception exc) {
					throw FillExceptionWithCommandInfo(exc, cmd);
				} finally {
					if (cmd != null && cmd.Parameters.Count > 0)
						cmd.Parameters.Clear();
				}
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the non query ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void ExecuteNonQueryEx(string connectionString, string sql, params object[] parameterValues){
			ExecuteNonQueryEx(connectionString,IsSPName(sql)?CommandType.StoredProcedure:CommandType.Text,sql,parameterValues);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the non query ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandType">			Type of the command. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void ExecuteNonQueryEx(string connectionString,CommandType commandType, string sql, params object[] parameterValues){
			while (true)
				try {
					if (commandType == CommandType.StoredProcedure) ExecuteNonQuery(connectionString, sql, parameterValues);
					else ExecuteNonQuery(connectionString, commandType, sql, PrepCommand(sql, parameterValues));
					break;
				} catch (SqlException exc) {
					if (!exc.Data.Contains("SQL")) exc.Data.Add("SQL", sql);
					if (!exc.Data.Contains("Params")) exc.Data.Add("Params", new StringList(parameterValues)+"");
					throw exc;
				}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the scalar ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecuteScalarEx(string connectionString, string sql, params object[] parameterValues){
			try {
				if (!IsSPName(sql)) return ExecuteScalar(connectionString, CommandType.Text, sql, PrepCommand(sql, parameterValues));
				return ExecuteScalar(connectionString, sql, parameterValues);
			} catch (Exception exc) {
				if (!exc.Data.Contains("SQL")) exc.Data.Add("SQL", sql);
				if (!exc.Data.Contains("Params")) exc.Data.Add("Params", new StringList(parameterValues) + "");
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the scalar array operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	A list of. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ArrayListEx ExecuteScalarArray(string connectionString, string sql, params object[] parameterValues){
			return ExecuteScalarArrayEx(connectionString,30,sql,parameterValues);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the scalar array ex operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandTimeout">		The command timeout. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		///
		/// <returns>	A list of. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ArrayListEx ExecuteScalarArrayEx(string connectionString, int commandTimeout, string sql, params object[] parameterValues){
			DataRowCollection rows = ExecuteDatasetEx(connectionString,commandTimeout,sql,parameterValues).Tables[0].Rows;
			ArrayListEx sl = new ArrayListEx();
			foreach( DataRow row in rows )
				sl.Add(row[0]);
			return sl;
		}
		#endregion

		#region XmlInnerText

			#region XmlInnerText

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(string connectionString, string sql) {
			return XmlInnerText(connectionString, sql, new object[] { });
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="sqlParams">				Options for controlling the sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(string connectionString, string sql, params object[] sqlParams) {
			return XmlInnerText(connectionString,sql,false,sqlParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="addEmpty">					true to add empty. </param>
		/// <param name="sqlParams">				Options for controlling the sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(string connectionString,string sql,bool addEmpty,params object[] sqlParams){
			return XmlInnerText(ExecuteDatasetEx(connectionString,sql,sqlParams),addEmpty);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(DataSet ds){return XmlInnerText(ds,false);}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">				The ds. </param>
		/// <param name="addEmpty">	true to add empty. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(DataSet ds,bool addEmpty){
			DataSet dsXml = new DataSet();
			foreach( DataTable dt in ds.Tables){
				if( addEmpty )dt.Rows.Add(dt.NewRow());
				dsXml.Tables.Add(XmlInnerTextPrepare(dt));
			}
			SetColumnsMapping(dsXml,MappingType.Attribute);
			return dsXml.GetXml();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(DataTable dt){return XmlInnerText(dt,false);}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">				. </param>
		/// <param name="addEmpty">	true to add empty. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(DataTable dt,bool addEmpty){
			if( dt.DataSet == null ) new DataSet().Tables.Add(dt);
			else{
				DataSet ds = new DataSet();
				ds.Tables.Add(dt.Copy());
				dt = ds.Tables[0];
			}
			return XmlInnerText(dt.DataSet,addEmpty);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dr">	The dr. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlInnerText(DataRow dr){
			DataTable dt = dr.Table.Clone();
			dt.Rows.Add(dr.ItemArray);
			return XmlInnerText(XmlInnerTextPrepare(dt));
		}
			#endregion

			#region XmlWithSchema

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml with schema. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlWithSchema(DataTable dt){
			dt.DataSet.Tables.Remove(dt);
			new DataSet().Tables.Add(dt);
			return XmlWithSchema(dt.DataSet);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml with schema. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlWithSchema(DataSet ds){
			IO.StringWriter sw = new IO.StringWriter();
			SetColumnsMapping(ds,MappingType.Attribute).WriteXml(sw,XmlWriteMode.WriteSchema);
			return sw.ToString();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml with schema. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="sqlParams">				Options for controlling the sql. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string XmlWithSchema(string connectionString,string sql,params object[] sqlParams){
			IO.StringWriter sw = new IO.StringWriter();
			SetColumnsMapping(ExecuteDatasetEx(connectionString,sql,sqlParams),MappingType.Attribute).WriteXml(sw,XmlWriteMode.WriteSchema);
			return sw.ToString();
		}
			#endregion

			#region Helpers

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the columns. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="cols">	The cols. </param>
		///
		/// <returns>	The columns. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static string[] GetColumns(DataColumn[] cols){
			ArrayListEx alCols = new ArrayListEx();
			foreach( DataColumn col in cols )
				alCols.Add(col.ColumnName);
			return alCols;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the relations. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		///
		/// <returns>	The relations. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataRelation[] GetRelations(DataSet ds){
			ArrayList alRels = new ArrayList();
			foreach( DataRelation rel in ds.Relations )
				alRels.Add(new DataRelation(rel.RelationName,rel.ParentTable.TableName,rel.ChildTable.TableName,GetColumns(rel.ParentColumns),GetColumns(rel.ChildColumns),rel.Nested));
			return (DataRelation[])alRels.ToArray(typeof(DataRelation));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xmlize data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataSet XmlizeDataSet(DataSet ds){
			DataSet dsXml = new DataSet();
			foreach( DataTable dt in ds.Tables )
				dsXml.Tables.Add(XmlizeDataTable(dt));
			return dsXml;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xmlize data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataTable XmlizeDataTable(DataTable dt){
			if (IsTableInRelation(dt)) throw new Exception("Table [" + dt.TableName + "] is in relation and can't be serialized.");
			DataTable dtXml = dt.Clone();
			foreach( DataColumn col in dtXml.Columns ){
				col.DataType = typeof(string);
				col.AllowDBNull = true;
			}
			foreach(DataRow row in dt.Rows) {
				List<string> lst = new List<string>();
				foreach(object o in row.ItemArray)
					lst.Add(o + "");
				try { dtXml.Rows.Add(lst.ToArray()); } catch { }
			}
			return dtXml;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Query if 'dt' is table in relation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	true if table in relation, false if not. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static bool IsTableInRelation(DataTable dt) {
			if (dt.DataSet == null) return false;
			foreach (DataRelation rel in dt.DataSet.Relations)
				if (rel.ParentTable == dt && dt.Rows.Count > 0 && rel.ChildTable.Rows.Count > 0) return true;
				else if (rel.ChildTable == dt && dt.Rows.Count > 0 && rel.ParentTable.Rows.Count > 0) return true;
			return false;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text prepare. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataSet">	Set the data belongs to. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataSet XmlInnerTextPrepare(DataSet dataSet){
			DataSet dsXml = new DataSet(dataSet.DataSetName);
			foreach( DataTable table in dsXml.Tables )
				dsXml.Tables.Add(XmlInnerTextPrepare(table));
			return dsXml;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text prepare. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static DataTable XmlInnerTextPrepare(DataTable dt){
			DataTable dtXml = XmlizeDataTable(dt);
			foreach( DataRow row in dtXml.Rows )
				XmlInnerTextPrepare(row);
			return dtXml;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Xml inner text prepare. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="row">	. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void XmlInnerTextPrepare(DataRow row){
			for(int r = 0; r < row.ItemArray.Length; r++ )
				if( row[r] == DBNull.Value ){
					if(row.Table.Columns[r].DataType == typeof(string) ||
						row.Table.Columns[r].DataType == typeof(Guid))row[r] = row.Table.Columns[r].ColumnName.ToLower() == "id" ? "0" : "";
					if(row.Table.Columns[r].DataType == typeof(int)){
						if( row.Table.Columns[r].AutoIncrement || IsColumnPK(row.Table.Columns[r]) )
							row[r] = 0;
						else{
							if( row.Table.Columns[r].DataType != typeof(string) )
								row.Table.Columns[r].DataType = typeof(string);
							row[r] = "";
						}
					}
				}
		}
			#endregion
		#endregion

		#region UpdateDataSet ==================
			#region FillDataSet

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Fill data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsDest">	The ds destination. </param>
		/// <param name="dsSrc">	The ds source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void FillDataSet(DataSet dsDest, DataSet dsSrc){
			for( int i = 0; i < dsDest.Tables.Count && i < dsSrc.Tables.Count; i++ )
				FillTable(dsDest.Tables[i],dsSrc.Tables[i]);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Fill table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dtDest">	The dt destination. </param>
		/// <param name="dtSrc">	The dt source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void FillTable(DataTable dtDest,DataTable dtSrc){
			FillRowCollection(dtDest.Rows,dtSrc.Rows);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Fill row collection. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="rowsDest">	The rows destination. </param>
		/// <param name="rowsSrc">	The rows source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void FillRowCollection(DataRowCollection rowsDest,DataRowCollection rowsSrc){
			foreach( DataRow rowSrc in rowsSrc )
				rowsDest.Add(rowSrc.ItemArray);
		}
			#endregion FillDataSet
			#region LoadDataTable

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="fields">	The fields. </param>
		///
		/// <returns>	The table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable LoadTable(IDictionary fields){ return LoadTable( fields,"" );	}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="fields">			The fields. </param>
		/// <param name="tableName">	Name of the table. </param>
		///
		/// <returns>	The table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable LoadTable( IDictionary fields,string tableName ){
			DataTable dt = new DataTable(tableName != "" ? tableName : "Table" );
			foreach( string colName in fields.Keys ){
				CheckInjects(colName);
				if( fields[colName] == null || fields[colName] == DBNull.Value )dt.Columns.Add(colName);
				else dt.Columns.Add(colName,fields[colName].GetType()).DefaultValue = fields[colName];
			}
			dt.Rows.Add(dt.NewRow());
			return dt;
			/*
			if( tableName == "" )tableName = "Table";
			DataTable table = new DataTable();
			foreach( DictionaryEntry field in fields )
				table.Columns.Add(field.Key.ToString(),field.Value.GetType());
			object[] values = new object[fields.Values.Count];
			fields.Values.CopyTo(values,0);
			table.Rows.Add(values);
			return table;
			*/
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlElement">	Element describing the xml. </param>
		///
		/// <returns>	The data table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable LoadDataTable( XmlElement xmlElement ){
			return LoadDataTable(xmlElement.InnerXml);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlDataSet">	Set the xml data belongs to. </param>
		///
		/// <returns>	The data table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable LoadDataTable( string xmlDataSet ){
			return LoadDataSet(xmlDataSet).Tables[0];
		}
			#endregion
	
			#region LoadDataSet

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="fields">	The fields. </param>
		///
		/// <returns>	The data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet LoadDataSet(Hashtable fields){
			DataSet ds = new DataSet();
			ds.Tables.Add(LoadTable(fields));
			return ds;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a custom data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ASPException">	Thrown when asp. </exception>
		///
		/// <param name="dsCustom">	The ds custom. </param>
		/// <param name="xml">			The xml. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void LoadCustomDataSet(DataSet dsCustom,string xml){
			try{
				XmlDataDocument xmlDS = new XmlDataDocument(dsCustom);
				xmlDS.LoadXml(xml);
			}catch(Exception exc){ throw new ASPException(exc,"XML",xml); }
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlElement">	Element describing the xml. </param>
		///
		/// <returns>	The data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet LoadDataSet( XmlElement xmlElement ){
			return LoadDataSet(new DataSet(),xmlElement);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">					The ds. </param>
		/// <param name="xmlElement">	Element describing the xml. </param>
		///
		/// <returns>	The data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet LoadDataSet( DataSet ds, XmlElement xmlElement ){
			ds.ReadXml(new XmlNodeReader(xmlElement));
			return ds;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlDataSet">	Set the xml data belongs to. </param>
		///
		/// <returns>	The data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet LoadDataSet( string xmlDataSet ){return LoadDataSet(	new DataSet(),xmlDataSet );}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Loads a data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">					The ds. </param>
		/// <param name="xmlDataSet">	Set the xml data belongs to. </param>
		///
		/// <returns>	The data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet LoadDataSet(DataSet ds,string xmlDataSet ){
			ds.ReadXml(new XmlTextReader(xmlDataSet, XmlNodeType.Element, new XmlParserContext(null, new XmlNamespaceManager(new NameTable()), null, XmlSpace.None)));
			return ds;
		}
			#endregion

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Inserts the fields. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ConnectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="nameValue">				A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable InsertFields(string ConnectionString, string sql, params object[] nameValue) {
			return UpdateFields(ConnectionString,sql, null as IDictionary, nameValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ConnectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="postFix">					The post fix. </param>
		/// <param name="keys">							The keys. </param>
		/// <param name="nameValue">				A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateFields(string ConnectionString, string sql, string postFix, IDictionary keys, params object[] nameValue) {
			return UpdateFields(ConnectionString, 0, sql, postFix, keys, nameValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ConnectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="keys">							The keys. </param>
		/// <param name="nameValue">				A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateFields(string ConnectionString, string sql, IDictionary keys, params object[] nameValue) {
			return UpdateFields(ConnectionString, 0, sql, keys, nameValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ConnectionString">			The connection string. </param>
		/// <param name="UpdateCommandTimeout">	The update command timeout. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="keys">									The keys. </param>
		/// <param name="nameValue">						A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateFields(string ConnectionString, int UpdateCommandTimeout, string sql, IDictionary keys, params object[] nameValue) {
			return UpdateFields(ConnectionString, UpdateCommandTimeout, sql, null, keys, nameValue);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the fields. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ConnectionString">			The connection string. </param>
		/// <param name="UpdateCommandTimeout">	The update command timeout. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="PostFix">							The post fix. </param>
		/// <param name="keys">									The keys. </param>
		/// <param name="nameValue">						A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateFields(string ConnectionString, int UpdateCommandTimeout, string sql, string PostFix, IDictionary keys, params object[] nameValue) {
			OrderedDictionaryEx fields = new OrderedDictionaryEx(nameValue);
			KeysFilter kf = new KeysFilter(keys);
			if (!Regex.IsMatch(sql, "^\\s*SELECT\\s", RegexOptions.IgnoreCase)) {
				sql = "SELECT " + (kf.KeysList == "" ? "" : kf.KeysList+ ",") + fields.KeyList(true) + " FROM " + sql;
			}
			if (kf.Filter != "") sql += " WHERE " + kf.Filter;
			DataTable dt = null;
			try {
				dt = UpdateDataTable(fields, ConnectionString, UpdateCommandTimeout, sql + (PostFix == null ? "" : ";" + PostFix), kf.Params);
			} catch (MultipleBaseTableUpdateException) {
				foreach (DictionaryEntry de in fields) {
					OrderedDictionary field = new OrderedDictionary();
					field.Add(de.Key, de.Value);
					dt = UpdateDataTable(field, ConnectionString, sql, kf.Params);
				}
				if (sql != "") dt = ExecuteDatasetEx(ConnectionString, sql, kf.Params).Tables[0];
			}
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the table with identifier. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">	. </param>
		/// <param name="applID">			Identifier for the appl. </param>
		/// <param name="tableName">	Name of the table. </param>
		/// <param name="id">					The identifier. </param>
		/// <param name="fieldName">	Name of the field. </param>
		/// <param name="fieldValue">	. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateTableWithID(string connection,int applID,string tableName,int id,string fieldName,string fieldValue){
				Hashtable ht = new Hashtable();
				ht.Add(Config.db.ApplID,applID);
				ht.Add("ID",id);
				ht.Add(fieldName,fieldValue);
				UpdateDataTable(ht,connection,"SELECT * FROM "+tableName+" WHERE ID=@ID",id);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="htData">			Information describing the height. </param>
		/// <param name="connString">	The connection string. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(IDictionary htData, string connString, string sql, params object[] parameters) {
			return UpdateDataTable(LoadTable(htData), connString, 0, sql, parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="htData">					Information describing the height. </param>
		/// <param name="connString">			The connection string. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="sql">						The sql. </param>
		/// <param name="parameters">			. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(IDictionary htData, string connString, int commandTimeout, string sql, params object[] parameters) {
			return UpdateDataTable(LoadTable(htData), connString, commandTimeout, sql, parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="htData">			Information describing the height. </param>
		/// <param name="conn">				The connection. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(IDictionary htData, SqlConnection conn, string sql, params object[] parameters) {
			return UpdateDataTable(LoadTable(htData),conn,sql,parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataRow">		The data row. </param>
		/// <param name="connection">	. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataRow dataRow, string connection, string sql, params object[] parameters) {
			DataSet ds = new DataSet();
			ds.Merge(new DataRow[]{dataRow});
			return UpdateDataTable(ds.Tables[0], connection, 0, sql, parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">	The data table. </param>
		/// <param name="connection">	. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable, string connection, string sql, params object[] parameters) {
			return UpdateDataTable(dataTable, connection, 0, sql, parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">			The data table. </param>
		/// <param name="connection">			. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="sql">						The sql. </param>
		/// <param name="parameters">			. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable, string connection, int commandTimeout, string sql, params object[] parameters) {
			using (SqlConnection conn = new SqlConnection(connection)) {
				return UpdateDataTable(dataTable, conn, null,commandTimeout, sql, parameters);
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">			The data table. </param>
		/// <param name="tran">						The tran. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="sql">						The sql. </param>
		/// <param name="parameters">			. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable, SqlTransaction tran, int commandTimeout, string sql, params object[] parameters) {
			return UpdateDataTable(dataTable,tran.Connection,tran,sql,parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">	The data table. </param>
		/// <param name="conn">				The connection. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable,SqlConnection conn,string sql,params object[] parameters) {
			return UpdateDataTable(dataTable,conn,null,sql,parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">	The data table. </param>
		/// <param name="conn">				The connection. </param>
		/// <param name="tran">				The tran. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable, SqlConnection conn, SqlTransaction tran, string sql, params object[] parameters) {
			return UpdateDataTable(dataTable, conn, tran,0, sql,  parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="MultipleBaseTableUpdateException">	Thrown when multiplebasetableupdate. </exception>
		/// <exception cref="SqlException">											Thrown when an error is generated by the
		/// 																										SQL server. </exception>
		/// <exception cref="InvalidOperationException">				Thrown when the requested operation is
		/// 																										invalid. </exception>
		///
		/// <param name="dataTable">			The data table. </param>
		/// <param name="conn">						The connection. </param>
		/// <param name="tran">						The tran. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="sql">						The sql. </param>
		/// <param name="parameters">			. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable UpdateDataTable(DataTable dataTable, SqlConnection conn, SqlTransaction tran, int commandTimeout, string sql, params object[] parameters) {
			SqlCommand comm = new SqlCommand();
			RowUpdateInfo rui = null;
			DataTable dt = null;
			while (true)
				try {
					comm = PrepareCommand(conn, sql.Split(';')[0], parameters);
					if (tran != null) comm.Transaction = tran;
					SqlDataAdapter da = new SqlDataAdapter(comm);
					DataSet ds = new DataSet();//				da.FillSchema(ds,SchemaType.Mapped);
					da.Fill(ds);
					dt = ds.Tables[0];
					SqlCommandBuilder cb = new SqlCommandBuilder(da);
					rui = new RowUpdateInfo(commandTimeout, sql, comm.Parameters);
					da.RowUpdating += new SqlRowUpdatingEventHandler(rui.da_RowUpdating);
					UpdateDataTable(dt, dataTable);
					da.Update(dt);
					break;
				} catch (SqlException exc) {
					if (exc.Number == 3609 && exc.Errors.Count == 1) break;
					if (exc.Number == 1205 && tran == null) {
						conn.Close();
						ASPException.Publish(exc, "SQL(DeadLock)", sql, "Params", new StringList(parameters));
						conn.Open();
						continue;
					}
					if (exc.Number == 4405) throw new MultipleBaseTableUpdateException();
					if (dataTable.DataSet == null) new DataSet().Tables.Add(dataTable);
					FillExceptionWithCommandInfo(exc, comm);
					if (rui != null) FillExceptionWithCommandInfo(exc, rui.command);
					exc.Data.Add("Table", dataTable.DataSet.GetXml());
					throw exc;
				} catch (InvalidOperationException exc) {
					if (dataTable.DataSet == null) new DataSet().Tables.Add(dataTable);
					FillExceptionWithCommandInfo(exc, comm);
					if (rui != null) FillExceptionWithCommandInfo(exc, rui.command);
					exc.Data.Add("Table", dataTable.DataSet.GetXml());
					throw exc;
				}
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Information about the row update. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private class RowUpdateInfo {
			string sql;
			int CommandTimeout;
			SqlParameterCollection parameters;
			public SqlCommand command = null;
			public RowUpdateInfo(int commandTimeout,string sql,SqlParameterCollection parameters) {
				CommandTimeout = commandTimeout;
				this.sql = sql;
				this.parameters = parameters;
			}
			public void da_RowUpdating(object sender,SqlRowUpdatingEventArgs e) {
				if(e.Command == null) return;
				command = e.Command;
				e.Command.CommandTimeout = CommandTimeout > 0 ? CommandTimeout : 120;
				if(e.Command.CommandText.StartsWith("INSERT ")) {
					SqlDataAdapter da = sender as SqlDataAdapter;
					DataColumn autoIncrementColumn = GetAutoIncrementColumn(da);
					if(autoIncrementColumn == null) return;
					if (Regex.IsMatch(da.SelectCommand.CommandText, @"\s*DELETE\s+FROM", RegexOptions.IgnoreCase)) return;
					e.Command.CommandText += "; " +
						Regex.Split(da.SelectCommand.CommandText," WHERE ",RegexOptions.IgnoreCase)[0] +
						" WHERE " + autoIncrementColumn.ColumnName + " = SCOPE_IDENTITY()";
					e.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
				} else if( sql.Split(';').Length > 1 ){
					string postSQL = sql.Split(';')[1];
					e.Command.CommandText = e.Command.CommandText + "; " + (postSQL != "" ? postSQL : sql.Split(';')[0]);
					foreach(SqlParameter param in parameters)
						e.Command.Parameters.Add(param.ParameterName,param.SqlDbType).Value = param.Value;
					e.Command.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
				}
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets an automatic increment column. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="da">	The da. </param>
		///
		/// <returns>	The automatic increment column. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected static DataColumn GetAutoIncrementColumn(SqlDataAdapter da) {
			DataSet ds = new DataSet();
			da.FillSchema(ds, SchemaType.Mapped);
			return GetAutoIncrementColumn(ds.Tables[0].Columns);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets an automatic increment column. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="columns">	The columns. </param>
		///
		/// <returns>	The automatic increment column. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		protected static DataColumn GetAutoIncrementColumn(DataColumnCollection columns) {
			foreach(DataColumn column in columns)
				if(column.AutoIncrement) return column;
			return null;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">	The data table. </param>
		/// <param name="connection">	. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateDataSet(DataTable dataTable,string connection,string sql,params object[] parameters){
			DataSet ds = new DataSet();
			ds.Tables.Add(dataTable.Copy());
			UpdateDataSet(ds,connection,sql,parameters);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="dataSet">		Set the data belongs to. </param>
		/// <param name="connection">	. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="parameters">	. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateDataSet(DataSet dataSet,string connection,string sql,params object[] parameters){
				using (SqlConnection conn = new SqlConnection(connection)) {
					SqlCommand comm = PrepareCommand(conn, sql, parameters);
					SqlDataAdapter da = new SqlDataAdapter(comm);
					DataSet ds = new DataSet();
					try {
						da.Fill(ds);
						SqlCommandBuilder cb = new SqlCommandBuilder(da);
						UpdateDataSet(ds, dataSet);
						//throw new Exception(dataSet.GetXml());
						da.Update(ds);
					} catch (Exception exc) {
						FillExceptionWithCommandInfo(exc, comm);
						throw;
					}
				}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsDest">	The ds destination. </param>
		/// <param name="dsSrc">	The ds source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void UpdateDataSet(DataSet dsDest,DataSet dsSrc){
			for( int i=0,tablesCount = dsDest.Tables.Count; i < tablesCount; i++ )
				UpdateDataTable(dsDest.Tables[i],dsSrc.Tables[i]);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dtDest">	The dt destination. </param>
		/// <param name="dtSrc">	The dt source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateDataTable(DataTable dtDest,DataTable dtSrc){
			for( int i = 0;i < dtDest.Rows.Count && i < dtSrc.Rows.Count; i++ )
				UpdateDataRow(dtDest.Rows[i],dtSrc.Rows[i]);
			while( dtDest.Rows.Count < dtSrc.Rows.Count ){
				DataRow row = dtDest.NewRow();
				UpdateDataRow(row,dtSrc.Rows[dtDest.Rows.Count]);
				dtDest.Rows.Add(row);
			}
			for( int i = dtDest.Rows.Count; i > dtSrc.Rows.Count; )
				dtDest.Rows[--i].Delete();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the data row. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="drDest">	The dr destination. </param>
		/// <param name="drSrc">	The dr source. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateDataRow(DataRow drDest,DataRow drSrc){
			DataColumnCollection cols = drSrc.Table.Columns;
			DataColumnCollection colsDest = drDest.Table.Columns;
			DataColumn[] pks = drSrc.Table.PrimaryKey;
			foreach( DataColumn col in cols){
				string columnName = col.ColumnName;
				if( !drDest.Table.Columns.Contains(columnName) )columnName = drSrc.Table.TableName+col.ColumnName;
				if( drDest.Table.Columns.Contains(columnName) ){
					try{
						if (!col.ReadOnly && !col.AutoIncrement && !IsColumnPK(col)) {
							object fieldValue = ConvertField(drSrc[col.ColumnName], colsDest[col.ColumnName]);
							if (!CompareObjects(drDest[columnName], fieldValue))
								drDest[columnName] = drSrc[col.ColumnName] == DBNull.Value ? DBNull.Value : fieldValue;
						}
					}catch(Exception exc){
						throw new Exception("Column:"+col.ColumnName+Environment.NewLine+"Value:"+drSrc[col.ColumnName],exc);
					}
				}
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Compare objects. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="o1">	The first object. </param>
		/// <param name="o2">	The second object. </param>
		///
		/// <returns>	true if it succeeds, false if it fails. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static bool CompareObjects(object o1, object o2) {
			if (o1.GetType() == typeof(DateTime)) return Math.Abs(((DateTime)o1).Subtract(DateTime.Parse(o2+"")).TotalSeconds) < 1;
			return o1.GetHashCode() == o2.GetHashCode();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Convert field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="fieldValue">	. </param>
		/// <param name="colTo">			The col to. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static object ConvertField(object fieldValue,DataColumn colTo){
			if( colTo.DataType == typeof(Guid) )
				return (fieldValue+"") == "" ? DBNull.Value : (object)new Guid(fieldValue+"");
			if( colTo.DataType.IsValueType && (fieldValue+"") == "" )return colTo.DefaultValue;
			if( colTo.DataType != typeof(bool) )
				return Convert.ChangeType(fieldValue,colTo.DataType);
			switch(fieldValue.ToString().ToLower()){
				case "-1":
				case "1":
					return true;
				case "0":
					return false;
				default:
					return Convert.ToBoolean(fieldValue);
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Query if 'col' is column pk. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="col">	The col. </param>
		///
		/// <returns>	true if column pk, false if not. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static bool IsColumnPK(DataColumn col){
			DataColumn[] primKeys = col.Table.PrimaryKey;
			foreach( DataColumn pk in primKeys )
				if( col == pk )return true;
			return false;
		}
			#endregion UpdateDataSet ==================

		#region UpdateField

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="fieldName">				Name of the field. </param>
		/// <param name="fieldValue">				. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(string connectionString, string sql, string fieldName, object fieldValue){
			UpdateField(connectionString,sql,fieldName,fieldValue,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">			The connection string. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="fieldName">						Name of the field. </param>
		/// <param name="fieldValue">						. </param>
		/// <param name="updateAsSelectTable">	true to update as select table. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(string connectionString, string sql, string fieldName, object fieldValue,bool updateAsSelectTable){
			using( SqlConnection conn = new SqlConnection(connectionString) )
			{
				conn.Open();
				UpdateField(conn,sql,fieldName,fieldValue,updateAsSelectTable);
			}
		}
		// public static void UpdateField(string connectionString,string viewName,int applID,string
		// fieldName,object fieldValue){
		// 	UpdateField(connectionString,null,"SELECT * FROM "+viewName+" WHERE
		// 	"+Config.db.ApplID+"="+applID,fieldName,fieldValue);
		// } 
		public static void UpdateField(SqlConnection conn, string sql, string fieldName, object fieldValue){
			UpdateField(conn,sql,fieldName,fieldValue,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">									The connection. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="fieldName">						Name of the field. </param>
		/// <param name="fieldValue">						. </param>
		/// <param name="updateAsSelectTable">	true to update as select table. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(SqlConnection conn,string sql,string fieldName,object fieldValue,bool updateAsSelectTable) {
			UpdateField(conn,null,sql,fieldName,fieldValue,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ApplicationException">	Thrown when application. </exception>
		/// <exception cref="SqlException">					Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="conn">									The connection. </param>
		/// <param name="tran">									The tran. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="fieldName">						Name of the field. </param>
		/// <param name="fieldValue">						. </param>
		/// <param name="updateAsSelectTable">	true to update as select table. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(SqlConnection conn,SqlTransaction tran,string sql,string fieldName,object fieldValue,bool updateAsSelectTable) {
			fieldName = fieldName.Replace("_x0020_"," ");
			fieldName = fieldName.Replace("_x002F_","/");
			CheckInjects(fieldName);
			string baseTable = "";
			try{
				if( fieldValue != null && fieldValue.GetType().Name == "XmlNode[]" )
					fieldValue = ((XmlNode[])fieldValue)[0].Value;
				if( updateAsSelectTable ){
					string selectTable = BuildTableSelectFromView(sql,baseTable);
					UpdateDataTable(new HashTableEx(fieldName,fieldValue),conn,selectTable);
				}else{
					fieldName = DBObjectNameSafe(fieldName);
					string viewName = Regex.Match(sql,@"FROM\s+(\S+)").Groups[1].Value;
					Match mWhere = Regex.Match(sql," where .+",RegexOptions.IgnoreCase);
					string where = mWhere.Success ? mWhere.Value : "";
					if( Regex.IsMatch(where,";") )throw new ApplicationException("Invalid WHERE expression. Call the authority at once!\n*** "+where+" ***\n");
					string sqlParamName = "DEFAULT";
					if( fieldValue != null ) {
						where += " AND (" + fieldName + "<>@Field OR " + fieldName + " IS NULL)";
						sqlParamName = "@Field";
					} else fieldValue = "";
					string sqlUpdate = "UPDATE " + DBObjectNameSafe( viewName ) + " SET " + fieldName + " = " + sqlParamName + where;
					try {
						if (tran != null) ExecuteNonQuery(tran, CommandType.Text, sqlUpdate, new SqlParameter("@Field", fieldValue));
						else ExecuteNonQuery(conn, CommandType.Text, sqlUpdate, new SqlParameter("@Field", fieldValue));
					} catch (SqlException exc) {
						if (exc.Number != 8114 ||fieldValue == null || (fieldValue + "") != "") throw;
						//Error converting data type
						UpdateField(conn, tran, sql, fieldName, null, updateAsSelectTable);
					}
				}
			}catch(Exception exc){
				string msg = "";
				try{msg = baseTable+"."+fieldName+"["+fieldValue+"]("+fieldValue.GetType().Name+")";}catch{}
				if (!exc.Data.Contains("SQL")) exc.Data.Add("SQL", sql);
				if (!exc.Data.Contains("Params")) exc.Data.Add("Params", msg);
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a table select from view. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">			The select. </param>
		/// <param name="tableName">	Name of the table. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static string BuildTableSelectFromView(string select,string tableName){
			string viewName = Regex.Match(select,@"from\s+(\S+)\s",RegexOptions.IgnoreCase | RegexOptions.Compiled).Groups[1].Value;
			return Regex.Replace(select,@"from\s+"+viewName,"FROM "+tableName,RegexOptions.IgnoreCase | RegexOptions.Compiled);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="fields">						The fields. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(string connectionString,string sql,Hashtable fields) {
			using(SqlConnection conn = new SqlConnection(connectionString)) {
				conn.Open();
				SqlTransaction trun = conn.BeginTransaction();
				UpdateField(conn,sql,fields);
				trun.Commit();
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">			The connection string. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="fields">								The fields. </param>
		/// <param name="updateAsSelectTable">	true to update as select table. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(string connectionString, string sql, IDictionary fields, bool updateAsSelectTable) {
			using(SqlConnection conn = new SqlConnection(connectionString)) {
				conn.Open();
				SqlTransaction tran = conn.BeginTransaction();
				UpdateField(conn,tran,sql,fields,	updateAsSelectTable);
				tran.Commit();
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">									The connection. </param>
		/// <param name="tran">									The tran. </param>
		/// <param name="sql">									The sql. </param>
		/// <param name="fields">								The fields. </param>
		/// <param name="updateAsSelectTable">	true to update as select table. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(SqlConnection conn, SqlTransaction tran, string sql, IDictionary fields, bool updateAsSelectTable) {
			foreach(DictionaryEntry field in fields)
				UpdateField(conn, tran, sql, (string)field.Key, field.Value, updateAsSelectTable);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Updates the field. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">		The connection. </param>
		/// <param name="sql">		The sql. </param>
		/// <param name="fields">	The fields. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void UpdateField(SqlConnection conn,string sql,Hashtable fields) {
			foreach(IDictionaryEnumerator field in fields)
				UpdateField(conn,sql,(string)field.Key,field.Value);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	This method assigns an array of values to an array of SqlParameters. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="commandParameters">	array of SqlParameters to be assigned values. </param>
		/// <param name="parameterValues">		array of objects holding the values to be assigned. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static void AssignParameterValues(SqlParameter[] commandParameters,object[] parameterValues) {
			for (int i = 0, j = commandParameters.Length, k = parameterValues.Length; i < j && i < k; i++) {
				commandParameters[i].Value = parameterValues[i];
			}
		}
		#endregion UpdateField

		#region ExecSP

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="procName">					Name of the proc. </param>
		/// <param name="arrayOfParams">		Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(string connectionString,string procName,params object[] arrayOfParams){
			return ExecSP(connectionString,30,procName,arrayOfParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandTimeout">		The command timeout. </param>
		/// <param name="procName">					Name of the proc. </param>
		/// <param name="arrayOfParams">		Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(string connectionString,int commandTimeout,string procName,params object[] arrayOfParams){
			using( SqlConnection conn = new SqlConnection(connectionString) ){
				conn.Open();
				return ExecSP(conn,commandTimeout,procName,arrayOfParams);
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="tran">						The tran. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(SqlTransaction tran,string procName,params object[] arrayOfParams)
		{
			return ExecSP(tran,30,procName,arrayOfParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="tran">						The tran. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(SqlTransaction tran,int commandTimeout, string procName,params object[] arrayOfParams)
		{
			return ExecSP(tran.Connection,tran,commandTimeout,procName,arrayOfParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(SqlConnection conn,string procName,params object[] arrayOfParams)
		{
			return ExecSP(conn,30,procName,arrayOfParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(SqlConnection conn,int commandTimeout,string procName,params object[] arrayOfParams)
		{
			return ExecSP(conn,null,commandTimeout,procName,arrayOfParams);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="ExecSpException">	Thrown when execsp. </exception>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="tran">						The tran. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP(SqlConnection conn, SqlTransaction tran, int commandTimeout, string procName, params object[] arrayOfParams) {
			SqlCommand comm = new SqlCommand();
			while (true)
				try {
					PrepareCommand(comm, conn, tran, CommandType.StoredProcedure, procName, SqlHelperParameterCache.GetSpParameterSet(conn.ConnectionString, procName, true));
					comm.CommandTimeout = commandTimeout;
					for (int i = 1; i < comm.Parameters.Count && i < arrayOfParams.Length + 1; i++)
						comm.Parameters[i].Value = arrayOfParams[i - 1] == null ? DBNull.Value : arrayOfParams[i - 1];
					comm.ExecuteNonQuery();
					return comm.Parameters[0].Value;
				} catch (SqlException exc) {
					if (exc.Number == 1205 && tran == null) {
						conn.Close();
						ASPException.Publish(exc, "SP(DeadLock)", procName, "Params", new StringList(arrayOfParams));
						conn.Open();
						continue;
					}
					FillExceptionWithCommandInfo(exc, comm);
					throw new ExecSpException(comm, exc);
				}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="tran">						The tran. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static object ExecSP_(SqlConnection conn,SqlTransaction tran,int commandTimeout,string procName,params object[] arrayOfParams) {
			SqlCommand comm;
			if( tran != null ){
				comm = GetCommandObject(procName,conn);
				comm.Transaction = tran;
			}else{
				comm = conn.CreateCommand();
				comm.CommandText = procName;
				comm.CommandType = CommandType.StoredProcedure;
				SqlCommandBuilder.DeriveParameters(comm);
			}
			comm.CommandTimeout = commandTimeout;
			for( int i=1; i < comm.Parameters.Count && i < arrayOfParams.Length+1; i++ )
				comm.Parameters[i].Value = arrayOfParams[i-1];
			comm.ExecuteNonQuery();
			return comm.Parameters[0].Value;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a command object. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="procName">	Name of the proc. </param>
		/// <param name="conn">			The connection. </param>
		///
		/// <returns>	The command object. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static SqlCommand GetCommandObject(string procName,SqlConnection conn)
		{
			SqlCommand comm;
			using(SqlConnection cn = new SqlConnection(conn.ConnectionString))
			{
				cn.Open();
				comm = new SqlCommand(procName,cn);
				comm.CommandType = CommandType.StoredProcedure;
				SqlCommandBuilder.DeriveParameters(comm);
			}
			comm.Connection = conn;
			return comm;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp data set operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandTimeout">		The command timeout. </param>
		/// <param name="procName">					Name of the proc. </param>
		/// <param name="arrayOfParams">		Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecSPDataSet(string connectionString,int commandTimeout,string procName,params object[] arrayOfParams){
			using (SqlConnection cn = new SqlConnection(connectionString)) {
				cn.Open();
				return ExecSPDataSet(cn, commandTimeout, procName, arrayOfParams);
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the sp data set operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="commandTimeout">	The command timeout. </param>
		/// <param name="procName">				Name of the proc. </param>
		/// <param name="arrayOfParams">	Options for controlling the array of. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet ExecSPDataSet(SqlConnection conn,int commandTimeout,string procName,params object[] arrayOfParams){
			SqlCommand comm = new SqlCommand(procName,conn);
			comm.CommandType = CommandType.StoredProcedure;
			comm.CommandTimeout = commandTimeout;
			SqlCommandBuilder.DeriveParameters(comm);
			for( int i=1; i < comm.Parameters.Count && i < arrayOfParams.Length+1; i++ ){
				comm.Parameters[i].Value = arrayOfParams[i-1];
			}
			SqlDataAdapter da = new SqlDataAdapter(comm);
			DataSet ds = new DataSet();
			da.Fill(ds);
			return ds;
		}
		#endregion ExecSP

		#region ExecuteXML

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Executes the xml operation. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="SqlException">	Thrown when an error is generated by the SQL server. </exception>
		///
		/// <param name="connectionString">	The connection string. </param>
		/// <param name="commandType">			Type of the command. </param>
		/// <param name="commandText">			The command text. </param>
		/// <param name="parameters">				. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string ExecuteXML(string connectionString, CommandType commandType, string commandText, params SqlParameter[] parameters){
			try {
				SqlDataReader dr = ExecuteReader(connectionString, commandType, commandText, parameters);
				StringBuilder sb = new StringBuilder();
				while (dr.Read())
					sb.Append(dr[0].ToString());
				return sb.ToString();
			} catch (SqlException exc) {
				exc.Data["Connection"] = connectionString;
				exc.Data["SQL"] = commandText;
				throw;
			}
		}
		#endregion

		#region SetColumnsMapping

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">	The ds. </param>
		/// <param name="mt">	The mt. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet SetColumnsMapping( DataSet ds,MappingType mt ){
			return SetColumnsMapping(ds,mt,null);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="ds">			The ds. </param>
		/// <param name="mt">			The mt. </param>
		/// <param name="colls">	The colls. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet SetColumnsMapping( DataSet ds,MappingType mt,string[] colls ){
			foreach( DataTable dt in ds.Tables )
				SetColumnsMapping(dt,mt,colls);
			return ds;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		/// <param name="mt">	The mt. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable SetColumnsMapping( DataTable dt, MappingType mt ){
			return SetColumnsMapping(dt,mt,null);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sets the columns mapping. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">			. </param>
		/// <param name="mt">			The mt. </param>
		/// <param name="colls">	The colls. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable SetColumnsMapping( DataTable dt, MappingType mt, string[] colls ){
			if( colls == null )
				foreach( DataColumn dc in dt.Columns )
					dc.ColumnMapping = mt;
			else
				foreach( string dc in colls )
					if( dt.Columns.Contains(dc) )
						dt.Columns[dc].ColumnMapping = mt;
			return dt;
		}
		#endregion

		#region Helpers

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Fill exception with command information. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="exc">	The exc. </param>
		/// <param name="comm">	The communications. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static Exception FillExceptionWithCommandInfo(Exception exc, SqlCommand comm) {
			if (exc == null || comm == null) return exc;
			if (exc.Data["Connection"] == null && comm.Connection != null ) exc.Data["Connection"] = comm.Connection.ConnectionString;
			exc.Data["CommandTimeout"] = comm.CommandTimeout;
			if (exc.Data[comm.CommandType + ""] == null)
				exc.Data.Add(comm.CommandType + "", comm.CommandText);
			else
				exc.Data[comm.CommandType + ""] += ";" + Environment.NewLine + comm.CommandText;
			foreach (SqlParameter param in comm.Parameters)
				exc.Data[param.ParameterName] = param.Value;
			return exc;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Makes the ds xml. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="name_value">	A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string MakeDSXml(params object[] name_value) { return XmlInnerText(MakeDS(name_value)); }

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Makes the ds. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="name_value">	A variable-length parameters list containing name value. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet MakeDS(params object[] name_value){
			DataTable dt = new DataSet().Tables.Add();
			for( int c = 0; c < name_value.Length; c += 2 )
				dt.Columns.Add(name_value[c]+"");
			ArrayList al = new ArrayList();
			for( int c = 1; c < name_value.Length; c += 2 )
				al.Add(name_value[c]);
			dt.Rows.Add(al.ToArray());
			return dt.DataSet;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables xml. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">						The ds to. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="dsFrom1">				The first ds from. </param>
		/// <param name="tableFromName1">	The first table from name. </param>
		/// <param name="dsFrom2">				The second ds from. </param>
		/// <param name="tableFromName2">	The second table from name. </param>
		/// <param name="dsFrom3">				The third ds from. </param>
		/// <param name="tableFromName3">	The third table from name. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string JoinTablesXml(
			DataSet dsTo	 ,string tableToName,
			DataSet dsFrom1,string tableFromName1,
			DataSet dsFrom2,string tableFromName2,
			DataSet dsFrom3,string tableFromName3
			){
			return XmlInnerText(
				JoinTables(
				JoinTables(dsTo,dsFrom1,dsFrom2,tableToName,tableFromName1,tableFromName2),
				dsFrom3,"",tableFromName3)
				);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables xml. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">						The ds to. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="dsFrom1">				The first ds from. </param>
		/// <param name="tableFromName1">	The first table from name. </param>
		/// <param name="dsFrom2">				The second ds from. </param>
		/// <param name="tableFromName2">	The second table from name. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string JoinTablesXml(DataSet dsTo,string tableToName,DataSet dsFrom1,string tableFromName1,DataSet dsFrom2,string tableFromName2){
			return XmlInnerText(JoinTables(dsTo,dsFrom1,dsFrom2,tableToName,tableFromName1,tableFromName2));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">						The ds to. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="dsFrom1">				The first ds from. </param>
		/// <param name="tableFromName1">	The first table from name. </param>
		/// <param name="dsFrom2">				The second ds from. </param>
		/// <param name="tableFromName2">	The second table from name. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(
			DataSet dsTo,string tableToName,
			DataSet dsFrom1,string tableFromName1,
			DataSet dsFrom2,string tableFromName2
			){
			return JoinTables(dsTo,dsFrom1,dsFrom2,tableToName,tableFromName1,tableFromName2);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">						The ds to. </param>
		/// <param name="dsFrom1">				The first ds from. </param>
		/// <param name="dsFrom2">				The second ds from. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="tableFromName1">	The first table from name. </param>
		/// <param name="tableFromName2">	The second table from name. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataSet dsTo,DataSet dsFrom1,DataSet dsFrom2,string tableToName,string tableFromName1,string tableFromName2){
			return JoinTables(
				JoinTables(dsTo.Tables[0],dsFrom1.Tables[0],tableToName,tableFromName1),
				dsFrom2,"",tableFromName2);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">		The ds to. </param>
		/// <param name="dtFrom">	The dt from. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataSet dsTo, DataTable dtFrom) {
			return JoinTables(dsTo.Tables[0], dtFrom, "", "");
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">		The ds to. </param>
		/// <param name="dsFrom">	The ds from. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataSet dsTo, DataSet dsFrom) {
			return JoinTables(dsTo, dsFrom, "", "");
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dsTo">						The ds to. </param>
		/// <param name="dsFrom">					The ds from. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="tableFromName">	Name of the table from. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataSet dsTo, DataSet dsFrom, string tableToName, string tableFromName) {
			return JoinTables(dsTo.Tables[0],dsFrom.Tables[0],tableToName,tableFromName);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dtTo">						The dt to. </param>
		/// <param name="dtFrom">					The dt from. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="tableFromName">	Name of the table from. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataTable dtTo,DataTable dtFrom,string tableToName,string tableFromName) {
			return JoinTables(dtTo,dtFrom,tableToName,tableFromName,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dtTo">						The dt to. </param>
		/// <param name="dtFrom">					The dt from. </param>
		/// <param name="tableToName">		Name of the table to. </param>
		/// <param name="tableFromName">	Name of the table from. </param>
		/// <param name="xmlize">					true to xmlize. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTables(DataTable dtTo,DataTable dtFrom,string tableToName,string tableFromName, bool xmlize){
			if( tableToName != "" )dtTo.TableName = tableToName;
			if( tableFromName != "") dtFrom.TableName = tableFromName ;
			if (dtFrom.DataSet != null) dtFrom.DataSet.Tables.Remove(dtFrom);
			if (dtTo.DataSet == null) new DataSet().Tables.Add(dtTo);
			dtTo.DataSet.Tables.Add(dtFrom);
			return dtTo.DataSet;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables ex xml. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="tablesAndNames">	List of names of the tables ands. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string JoinTablesExXml(params object[] tablesAndNames){
			return XmlInnerText(JoinTablesEx(tablesAndNames));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables ex. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="tablesAndNames">	List of names of the tables ands. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTablesEx(params object[] tablesAndNames) {
			return JoinTablesEx(false,tablesAndNames);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Join tables ex. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="xmlize">					true to xmlize. </param>
		/// <param name="tablesAndNames">	List of names of the tables ands. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet JoinTablesEx(bool xmlize,params object[] tablesAndNames) {
			DataSet dsTo = new DataSet();
			for( int i = 0; i < tablesAndNames.Length; i += 2 ){
				DataSet ds = tablesAndNames[i] as DataSet;
				DataTable dt = ds != null ? ds.Tables[0] : (DataTable)tablesAndNames[i];
				if( dt.DataSet != null )dt = dt.Copy();
				if( (string)tablesAndNames[i+1] != "" )dt.TableName = tablesAndNames[i+1]+"";
				dsTo.Tables.Add(dt);
			}
			return xmlize ? XmlizeDataSet(dsTo) : dsTo;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	If column does not exists, create it. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="row">				. </param>
		/// <param name="columnName">	. </param>
		///
		/// <returns>	true - if column had to be created. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static bool EnsureColumn(DataRow row,string columnName){return EnsureColumn(row,columnName,null);}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	If column does not exists, create it. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="row">				. </param>
		/// <param name="columnName">	. </param>
		/// <param name="fieldValue">	. </param>
		///
		/// <returns>	true - if column had to be created. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static bool EnsureColumn(DataRow row,string columnName,object fieldValue){
			bool ret = EnsureColumn(row.Table.Columns,columnName);
			if( fieldValue != null )row[columnName] = fieldValue;
			return ret;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	If column does not exists, create it. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="cols">				The cols. </param>
		/// <param name="columnName">	. </param>
		///
		/// <returns>	true - if column had to be created. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static bool EnsureColumn(DataColumnCollection cols,string columnName){
			if( cols.Contains(columnName) )return false;
			cols.Add(columnName);
			return true;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Initialises the table if empty. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">						. </param>
		/// <param name="fields">				The fields. </param>
		/// <param name="addEmptyRow">	true to add empty row. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable InitTableIfEmpty(DataTable dt,Hashtable fields,bool addEmptyRow){
			if( dt.Rows.Count == 0 || addEmptyRow ){
				DataRow row = dt.NewRow();
				foreach( object name in fields.Keys )
					row[name+""] = fields[name];
				dt.Rows.Add(row);
			}
			return dt;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sort data set. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataSet">	Set the data belongs to. </param>
		///
		/// <returns>	The sorted data set. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet SortDataSet(DataSet dataSet){return null;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Sort table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dataTable">	The data table. </param>
		/// <param name="sortBy">			Amount to sort by. </param>
		///
		/// <returns>	The sorted table. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataSet SortTable(DataTable dataTable,string sortBy){
			if( dataTable.DataSet == null )new DataSet().Tables.Add(dataTable);
			DataSet dsSorted = dataTable.DataSet.Clone();
			dsSorted.Merge(dataTable.Select("",sortBy));
			dataTable.Rows.Clear();
			dataTable.DataSet.Merge(dsSorted.Tables[dataTable.TableName]);
			return dataTable.DataSet;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Queries if a given sp exists. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">	. </param>
		/// <param name="spName">			Name of the sp. </param>
		///
		/// <returns>	true if it succeeds, false if it fails. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static bool SpExists(string connection,string spName){
			return ExecuteDatasetEx(connection,"exec sp_stored_procedures @Name",spName).Tables[0].Rows.Count > 0;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Check injects. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="texts">	A variable-length parameters list containing texts. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void CheckInjects(params string[] texts){
			foreach(string text in texts )
				CheckInjects(text);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Check injects. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="NoNullAllowedException">	Thrown when nonullallowed. </exception>
		/// <exception cref="Exception">							Thrown when exception. </exception>
		///
		/// <param name="text">	The text. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string CheckInjects(string text){
			try {
				if (text == null) throw new NoNullAllowedException("Null string is not allowed as a part of SQL statement.");
				if (Regex.IsMatch(text, "(;|--)", RegexOptions.Compiled)) throw new Exception("Hacker alert! Unit is on it's way.");
				if (Regex.IsMatch(text, @";.*(?:delete|update|select|insert|EXEC|DBCC|TRUNCATE|DROP|CODE\s*\()", RegexOptions.IgnoreCase | RegexOptions.Compiled)) throw new Exception("Hacker alert! Unit is on it's way.");
				return text;
			} catch (Exception exc) {
				exc.Data["Text"] = text;
				throw;
			}
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Row to hash table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="table">	The table. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static Hashtable RowToHashTable(DataTable table){return RowToHashTable(table.Rows[0]);}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Row to hash table. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="row">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static Hashtable RowToHashTable(DataRow row){
			Hashtable ht = new Hashtable();
			foreach( DataColumn col in row.Table.Columns )
				ht.Add(col.ColumnName,row[col]);
			return ht;
		}

			#region PrepareFilter

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prepare filter. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">						The connection. </param>
		/// <param name="dataSetFilter">	Element describing the data set filter. </param>
		/// <param name="select">					The select. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public WhereFilter PrepareFilter(string conn,XmlElement dataSetFilter,string select){
			ArrayList alWhere = new ArrayList();
			ArrayList alParams = new ArrayList();
			DataColumnCollection cols0 = SQLHelperEx.ExecuteDatasetEx(conn,select).Tables[0].Columns;
			DataRow row = LoadDataSet(dataSetFilter).Tables[0].Rows[0];
			DataColumnCollection cols = row.Table.Columns;
			foreach( DataColumn col in row.Table.Columns )
				if( row[col] != null && row[col].ToString() != "" ){
					alWhere.Add(col.ColumnName + " "+(row[col].GetType().IsPrimitive?"=":"LIKE")+" ?");
					alParams.Add(MakeFilterParamValue(row[col],cols0[col.ColumnName]));
				}
			WhereFilter wf = new WhereFilter(string.Join(" AND ",(string[])alWhere.ToArray(typeof(string))),alParams);
			return wf;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Makes a filter parameter value. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="param">	The parameter. </param>
		/// <param name="col">		The col. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private object MakeFilterParamValue(object param,DataColumn col){
			return col.DataType.IsPrimitive ? param : '%'+param.ToString()+'%'; 
		}
			#endregion

			#region TableExists

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Queries if a given table exists. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">	. </param>
		/// <param name="tableName">	Name of the table. </param>
		///
		/// <returns>	true if it succeeds, false if it fails. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static bool TableExists(string connection,string tableName){
			return ExecuteDataset(connection,CommandType.Text,"exec sp_tables "+tableName).Tables[0].Rows.Count > 0;
		}
			#endregion

			#region SELECTs

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	DataBind HtmlSelect control. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">						The select. </param>
		/// <param name="connString">				The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="valueField">				The value field. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SELECT(HtmlSelect select,string connString,string sql,string valueField, params object[] parameterValues){
			SELECT(select,connString,sql,valueField,valueField,parameterValues);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	DataBind HtmlSelect control. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">						The select. </param>
		/// <param name="connString">				The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="textField">				Mast be specified. </param>
		/// <param name="valueField">				The value field. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SELECT(HtmlSelect select,string connString,string sql,string textField,string valueField,params object[] parameterValues){
			SELECT(select,ExecuteDatasetEx(connString,sql,parameterValues),textField,valueField,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	DataBind HtmlSelect control. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">						The select. </param>
		/// <param name="connString">				The connection string. </param>
		/// <param name="sql">							The sql. </param>
		/// <param name="textField">				Mast be specified. </param>
		/// <param name="valueField">				The value field. </param>
		/// <param name="addEmpty">					true to add empty. </param>
		/// <param name="parameterValues">	A variable-length parameters list containing parameter values. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SELECT(HtmlSelect select,string connString,string sql,string textField,string valueField,bool addEmpty, params object[] parameterValues){
			SELECT(select,ExecuteDatasetEx(connString,sql,parameterValues),textField,valueField,addEmpty);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	DataBind HtmlSelect control. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">			The select. </param>
		/// <param name="dataSet">		Set the data belongs to. </param>
		/// <param name="textField">	Mast be specified. </param>
		/// <param name="valueField">	The value field. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SELECT(HtmlSelect select,DataSet dataSet,string textField,string valueField){
			SELECT(select,dataSet,textField,valueField,false);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	DataBind HtmlSelect control. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">			The select. </param>
		/// <param name="dataSet">		Set the data belongs to. </param>
		/// <param name="textField">	Mast be specified. </param>
		/// <param name="valueField">	The value field. </param>
		/// <param name="addEmpty">		true to add empty. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void SELECT(HtmlSelect select,DataSet dataSet,string textField,string valueField,bool addEmpty){
			if( addEmpty )dataSet.Tables[0].Rows.InsertAt(dataSet.Tables[0].NewRow(),0);
			select.DataSource = dataSet;
			select.DataTextField = textField;
			select.DataValueField = valueField;
			select.DataBind();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Build &lt;OPTION value='value'&gt;text%lh;/OPTION%gt;&lt;OPTION .. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="conn">				The connection. </param>
		/// <param name="sql">				The sql. </param>
		/// <param name="textField">	Mast be specified. </param>
		/// <param name="valueField">	Cat be "". </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string BuildSelectOptions(string conn,string sql,string textField,string valueField){
			DataTable dt = ExecuteDatasetEx(conn,sql).Tables[0];
			return BuildSelectOptions(dt,textField,valueField);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Build &lt;OPTION value='value'&gt;text%lh;/OPTION%gt;&lt;OPTION .. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">					. </param>
		/// <param name="textField">	Mast be specified. </param>
		/// <param name="valueField">	Cat be "". </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string BuildSelectOptions(DataTable dt,string textField,string valueField){
			XmlDocument doc = new XmlDocument();
			doc.LoadXml("<SELECT/>");
			foreach( DataRow row in dt.Rows ){
				XmlElement el = doc.CreateElement("OPTION");
				el.SetAttribute("value",row[valueField]+"");
				el.InnerText = row[textField]+"";
				doc.DocumentElement.AppendChild(el);
			}
			return doc.DocumentElement.InnerXml;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Build &lt;OPTION value='value'&gt;text%lh;/OPTION%gt;&lt;OPTION .. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">					. </param>
		/// <param name="textField">	Mast be specified. </param>
		/// <param name="valueField">	Cat be "". </param>
		/// <param name="styleField">	Can be "". </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string BuildSelectOptions(DataTable dt,string textField,string valueField,string styleField){
//			DataTable dtColor = SQL.ExecuteDatasetEx(db,"SELECT value,style,Name FROM ColorOptions() ORDER BY RGB DESC").Tables[0];
			DataColumnCollection cols = dt.Columns;
			if( styleField != "" ){
				cols[styleField].ColumnName = "style";
				cols["style"].ColumnMapping = MappingType.Attribute;
			}
			cols[textField].ColumnName = "text";
			if( valueField == "" )cols.Add("value",typeof(string),"text");
			else cols[valueField].ColumnName = "value";
			cols["value"].ColumnMapping = MappingType.Attribute;
			while( true ){
				try{
					foreach( DataColumn col in cols )
						if( !Regex.IsMatch(col.ColumnName,"(text|value|style)",RegexOptions.IgnoreCase) )
							cols.Remove(col);
					break;
				}catch(InvalidOperationException){continue;}
			}
			cols["text"].ColumnMapping = MappingType.SimpleContent;
			dt.TableName = "OPTION";
			if( dt.DataSet == null )new DataSet().Tables.Add(dt);
			return dt.DataSet.GetXml();
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Bind select. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="select">					The select. </param>
		/// <param name="ds">							The ds. </param>
		/// <param name="dataTextField">	The data text field. </param>
		/// <param name="dataValueField">	The data value field. </param>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static void BindSelect(HtmlSelect select,DataSet ds,string dataTextField,string dataValueField){
			select.DataSource = ds;
			select.DataTextField = dataTextField;
			select.DataValueField = dataValueField;
			select.DataBind();
		}
			#endregion

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets the columns. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connString">	The connection string. </param>
		/// <param name="sql">				The sql. </param>
		///
		/// <returns>	The columns. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static StringCollection GetColumns(string connString,string sql){
			StringCollection scCols = new StringCollection();
			using(SqlConnection conn = new SqlConnection(connString)){
				conn.Open();
				if( sql.IndexOf(" ") < 0 )sql = "SELECT * FROM " + sql;
				SqlCommand comm = new SqlCommand(sql,conn);
				SqlDataReader dr = comm.ExecuteReader(CommandBehavior.KeyInfo);
				DataTable dtSchema = dr.GetSchemaTable();
				foreach( DataRow row in dtSchema.Rows )
					scCols.Add(row["ColumnName"].ToString().ToLower());
				dr.Close();
			}
			return scCols;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Builds a update struct. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="row">					. </param>
		/// <param name="colsToUpdate">	The cols to update. </param>
		/// <param name="startIndex">		The start index. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static UpdateStruct BuildUpdateStruct(DataRow row,StringCollection colsToUpdate,int startIndex){
			DataTable table = row.Table;
			DataColumnCollection cols = table.Columns;
			ArrayList sets = new ArrayList();
			ArrayList sqlParams = new ArrayList();
			for( int i=startIndex; i < cols.Count; i++ ){
				DataColumn col = cols[i];
				if( col.ReadOnly )continue;
				if( colsToUpdate != null && !colsToUpdate.Contains(col.ColumnName.ToLower()) )continue;
				sets.Add("["+col.ColumnName+"] = @"+col.ColumnName);
				sqlParams.Add(new SqlParameter("@"+col.ColumnName,row[col.ColumnName]));
			}
			return new UpdateStruct("UPDATE ["+table.TableName+"] SET "+string.Join(",",(string[])sets.ToArray(typeof(string))),(SqlParameter[])sqlParams.ToArray(typeof(SqlParameter)));
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Removes the columns. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="table">		The table. </param>
		/// <param name="columns">	The columns. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static DataTable RemoveColumns(DataTable table,params string[] columns){
			foreach( string column in columns )
				if( table.Columns.Contains(column) )
					table.Columns.Remove(column);
			return table;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a column names string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	The column names string. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetColumnNamesString(DataTable dt){
			return "["+string.Join("],[",GetColumnNamesArray(dt))+"]";
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a column names array. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dt">	. </param>
		///
		/// <returns>	The column names array. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string[] GetColumnNamesArray(DataTable dt){
			string[] cols = new string[dt.Columns.Count];
			for( int i=0; i < dt.Columns.Count; i++ )
				cols[i] = dt.Columns[i].ColumnName;
			return cols;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prep sql. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string PrepSQL(string sql, params object[] paramsArray) {
			Regex re = new Regex("@\\w+", RegexOptions.ECMAScript);
			for (int i = 0; i < paramsArray.Length; i++)
				sql = re.Replace(sql, paramsArray[i] + "", 1);
			return sql;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a safe connection string. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connString">	The connection string. </param>
		///
		/// <returns>	The safe connection string. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetSafeConnString(string connString) {
			return Regex.Replace(connString,"Password=[^;]+","",RegexOptions.IgnoreCase);
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a safe sql. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="sql">	The sql. </param>
		///
		/// <returns>	The safe sql. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string GetSafeSQL(string sql){
			if( sql.IndexOf(";") >= 0 )
				throw new Exception("Hacker alert! Your IP address has been sent to the FBI.");
			return sql;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prep command. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <exception cref="Exception">	Thrown when exception. </exception>
		///
		/// <param name="sql">					The sql. </param>
		/// <param name="paramsArray">	Array of parameters. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static SqlParameter[] PrepCommand(string sql,params object[] paramsArray){
			try {
				if (paramsArray == null) return null;
				Regex re = new Regex(@"[\s><=(+,](?<param>@\w+)", RegexOptions.ECMAScript);
				MatchCollection mParams = re.Matches(sql);
				ArrayList alParams = new ArrayList();
				StringCollection sc = new StringCollection();
				int g = 0;
				foreach (Match mParam in mParams)
					if (!sc.Contains(mParam.Groups["param"].Value)) {
						sc.Add(mParam.Groups["param"].Value);
						if (paramsArray[g] != Missing.Value) {
							SqlParameter p = new SqlParameter(mParam.Groups["param"].Value, paramsArray[g] == null ? DBNull.Value : paramsArray[g]);
							if (p.SqlDbType == SqlDbType.NVarChar) p.SqlDbType = SqlDbType.VarChar;
							alParams.Add(p);
						}
						g++;
					}
				return (SqlParameter[])alParams.ToArray(typeof(SqlParameter));
			} catch (Exception exc) {
				exc.Data.Add("sql", sql);
				throw;
			}
		}

		#region PrepareCommand

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prepare command and parameters. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="connection">	. </param>
		/// <param name="sql">				. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static SqlCommand PrepareCommand(SqlConnection connection,string sql, params object[] parameters){
			SqlCommand comm = PrepareCommand(sql,parameters);
			if( connection.State == ConnectionState.Closed )
				connection.Open();
			comm.Connection = connection;
			return comm;
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Prepare command and parameters. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="sql">				. </param>
		/// <param name="parameters">	. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static SqlCommand PrepareCommand(string sql,params object[] parameters){
			SqlCommand comm = new SqlCommand(sql);
			Regex re = new Regex(@"(@\w+)",RegexOptions.ECMAScript);
			MatchCollection mParams = re.Matches(sql);
			for( int g=0; g < mParams.Count && g < parameters.Length; g++ )
				comm.Parameters.Add(mParams[g].Value,parameters[g]==null?DBNull.Value:parameters[g]);
			return comm;
		}
		#endregion

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Database object name safe. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="dbObjectName">	Name of the database object. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static string DBObjectNameSafe(string dbObjectName){
			dbObjectName = dbObjectName.Trim();
			return dbObjectName.IndexOf("[")==0 ? dbObjectName : "["+dbObjectName+"]";
		}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Gets a command type. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="sql">	The sql. </param>
		///
		/// <returns>	The command type. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static CommandType GetCommandType(string sql){return IsSPName(sql) ? CommandType.StoredProcedure : CommandType.Text;}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Query if 'sql' is sp name. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="sql">	The sql. </param>
		///
		/// <returns>	true if sp name, false if not. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		private static bool IsSPName(string sql){return !Regex.IsMatch(sql,"^\\s*(EXEC |UPDATE |INSERT |SELECT |DELETE |CREATE )",RegexOptions.IgnoreCase);}

		////////////////////////////////////////////////////////////////////////////////////////////////////
		/// <summary>	Makes an error rs. </summary>
		///
		/// <remarks>	Dimon, 12/21/2010. </remarks>
		///
		/// <param name="exc">	The exc. </param>
		///
		/// <returns>	. </returns>
		////////////////////////////////////////////////////////////////////////////////////////////////////

		public static ADODB.RecordsetClass MakeErrorRS(Exception exc){
			Missing e = Missing.Value;
			ADODB.RecordsetClass rs = new ADODB.RecordsetClass();
			rs.Fields.Append("ErrorNumber",ADODB.DataTypeEnum.adVarChar,20,ADODB.FieldAttributeEnum.adFldUnspecified,null);
			rs.Fields.Append("ErrorDesc",ADODB.DataTypeEnum.adVarChar,4096,ADODB.FieldAttributeEnum.adFldUnspecified,null);
			rs.Fields.Append("ErrorSrc",ADODB.DataTypeEnum.adVarChar,4096,ADODB.FieldAttributeEnum.adFldUnspecified,null);
			
			rs.Open(e,e,ADODB.CursorTypeEnum.adOpenUnspecified,ADODB.LockTypeEnum.adLockUnspecified,0);
			rs.AddNew(e,e);
			rs.Fields[0].Value = "!";
			rs.Fields[1].Value = "<div style='color:crimson'>"+exc.Message+"</div>";
			rs.Fields[2].Value = exc.Source;
			return rs;
		}

		#endregion Helpers

	}
	#region Exception Classes

	////////////////////////////////////////////////////////////////////////////////////////////////////
	/// <summary>	Exception for signalling execute sp errors. </summary>
	///
	/// <remarks>	Dimon, 12/21/2010. </remarks>
	////////////////////////////////////////////////////////////////////////////////////////////////////

	public class ExecSpException : Exception{
		public int ReturnValue = 0;
		internal ExecSpException(SqlCommand command,Exception exc):base("",exc){
			ReturnValue = command.Parameters.Count > 0 ? Dimok.Common.ParseInt(command.Parameters[0].Value) : 0;
		}
	}
	#endregion
}
/*
using ADODB;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;

#region Recordset CreateADODBRecordSet()
/// <summary>
/// Accesses the Datasource to Return an ADODB Recordset
/// </summary>
///<remarks>Public Recordset CreateADODBRecordSet()</remarks>
/// <returns>Recordset</returns>
public Recordset CreateADODBRecordSet()
{
DataTable oDataTable = new DataTable();
Recordset oRS = new Recordset();
try
{
oDataTable = CreateDataTable();
//Loop through each column in the Dataset
foreach(DataColumn oColumn in oDataTable.Columns)
{
//Create the Field Types for the recordset

oRS.Fields.Append(oColumn.ColumnName,GetADOType(oColumn.DataType.ToString())
,GetADOTypeSize(oColumn.DataType.ToString()),FieldAttributeEnum.adFldIsNulla
ble,System.Reflection.Missing.Value);
}

//Open the recordset
oRS.Open(System.Reflection.Missing.Value,
System.Reflection.Missing.Value, CursorTypeEnum.adOpenKeyset,
LockTypeEnum.adLockOptimistic, 1);
//Loop through the table and fill the ADO Recordset
for(int i = 0; i < oDataTable.Rows.Count; i ++)
{
oRS.AddNew(System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
//Loop through each column
for(int j = 0; j < oDataTable.Columns.Count; j ++)
{
oRS.Fields[j].Value = oDataTable.Rows[i][j];
}
oRS.Update(System.Reflection.Missing.Value,
System.Reflection.Missing.Value);
}
//Move to the first record
oRS.MoveFirst();
return oRS;
}
catch(System.Exception e)
{
some error logging function
return null;
}
finally
{
oError.Dispose();
}

}
#endregion
/// <summary>
/// Return the ADO Data Type
/// </summary>
/// <remarks></remarks>
/// <param name="sType"></param>
/// <returns>Integer ADO Data Type</returns>
private DataTypeEnum GetADOType(string sType)
{
switch(sType)
{
case null:
//adEmpty 0
return DataTypeEnum.adEmpty;
case "System.SByte":
//adTinyInt 16
return DataTypeEnum.adTinyInt;
case "System.Boolean":
//adBoolean 11
return DataTypeEnum.adBoolean;
case "System.Int16":
//adSmallInt 2
return DataTypeEnum.adSmallInt;
case "System.Int32":
//adInteger 3
return DataTypeEnum.adInteger;
case "System.Int64":
//adBigInt 20
return DataTypeEnum.adBigInt;
case "System.Single":
//adSingle 4
return DataTypeEnum.adSingle;
case "System.Double":
//adDouble 5
return DataTypeEnum.adDouble;
case "System.Decimal":
//adDecimal 14
return DataTypeEnum.adDecimal;
case "System.DateTime":
//adDate 7
return DataTypeEnum.adDate;
case "System.Guid":
//adGUID 72
return DataTypeEnum.adGUID;
case "System.String":
//adChar 129
return DataTypeEnum.adChar;
case "System.byte[]":
//adBinary
return DataTypeEnum.adBinary;
default:
return 0;
}
}
/// <summary>
/// Return the ADO Type Size to be used in the appending of fields
/// </summary>
/// <param name="sType"></param>
/// <returns>Integer size of the ADO Type to be used.</returns>
private int GetADOTypeSize(string sType)
{
switch(sType)
{
case null:
//adEmpty 0
return -1;
case "System.SByte":
//adTinyInt 16
return -1;
case "System.Boolean":
//adBoolean 11
return -1;
case "System.Int16":
//adSmallInt 2
return -1;
case "System.Int32":
//adInteger 3
return -1;
case "System.Int64":
//adBigInt 20
return -1;
case "System.Single":
//adSingle 4
return -1;
case "System.Double":
//adDouble 5
return -1;
case "System.Decimal":
//adDecimal 14
return -1;
case "System.DateTime":
//adDate 7
return -1;
case "System.Guid":
//adGUID 72
return -1;
case "System.String":
//adChar 129
return 32767;
case "System.byte[]":
//adBinary
return 32767;
default:
return 1;
}
}
*/